//
//  SDataBaseManager.m
//  News
//
//  Created by dlios on 15-5-20.
//  Copyright (c) 2015年 PEAK. All rights reserved.
//

#import "SDataBaseManager.h"

@implementation SDataBaseManager

+ (SDataBaseManager *)shareInstance
{
    static SDataBaseManager *dbManager = nil;
    if (dbManager == nil) {
        dbManager = [[SDataBaseManager alloc] init];
        
    }
    return dbManager;
}

//打开数据库
- (void)openDB
{
    //数据库一般存放在documents文件夹下
    //获取documents文件路径
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    //拼接数据库路径
    NSString *dbPath = [path stringByAppendingPathComponent:@"yuedu.db"];
    
    //创建数据库
    //检测路径下 是否已经存在数据库  如果不存在 才创建(查看sqlite3指针是否指向了数据库)
    //参数1:本地数据库的路径(需要UTF-转码)
    //参数2:数据库指针地址
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    
    
    NSLog(@"打开结果 :%d, path : %@",result, dbPath);
}
- (void)createTable
{
    //1.创建sql=语句
    NSString *createSQL = @"create table if not exists yuedu(body text, title text, ref text, pixel text, src text, num integer primary key autoincrement)";
    //2.执行sql语句
    int result = sqlite3_exec(dbPoint, createSQL.UTF8String, NULL, NULL, nil);
    //3.判断
    if (SQLITE_OK == result) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}
//添加对象
- (void)insertInfoWithSReadshoucang:(SReadshoucang *)shoucang
{
    //1.sql语句
    NSString *insertStr = [NSString stringWithFormat:@"insert into yuedu (body, title, ref, src) values ('%@', '%@', '%@', '%@')", shoucang.body, shoucang.title, shoucang.ref, shoucang.src];
    
    //2.执行语句
    int result = sqlite3_exec(dbPoint, insertStr.UTF8String, NULL, NULL, nil);
    //3.判断
    if (SQLITE_OK == result) {
        NSLog(@"插入成功");
    }else {
        NSLog(@"插入失败");
    }
}
//查询操作
- (NSMutableArray *)selectInfo
{
    //查询操作逻辑
    //1.从本地数据库获取所有信息   遍历获取信息
    //2.把每条信息转化为Model对象
    //3.把Model添加在可变数组中返回
    
    
    //1.创建sql语句
    NSString *selectSQL = @"select * from yuedu";
    //2.创建数据库替身
    sqlite3_stmt *stmt = nil;
    //3.准备sql语句
    int result = sqlite3_prepare_v2(dbPoint, selectSQL.UTF8String, -1, &stmt, nil);
    //4.创建存放返回数据的信息数组
    NSMutableArray *arr = [NSMutableArray array];
    //5.判断查询准备
    if (SQLITE_OK == result) {
        NSLog(@"查询准备成功");
        
        //6.开始遍历每一行信息
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *body = sqlite3_column_text(stmt, 0);
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            
            const unsigned char *ref = sqlite3_column_text(stmt, 2);
            //const unsigned char *pixel = sqlite3_column_text(stmt, 3);
            const unsigned char *src = sqlite3_column_text(stmt, 3);
            int num = sqlite3_column_int(stmt, 4);
            
            //访问到的数据转化添加在Model中
           SReadshoucang *shoucang = [[[SReadshoucang alloc] init] autorelease];
           
           shoucang.body = [NSString stringWithUTF8String:(const char *) body];
            shoucang.title = [NSString stringWithUTF8String:(const char *)title];
            shoucang.ref = [NSString stringWithUTF8String:(const char *)ref];
            //shoucang.pixel = [NSString stringWithUTF8String:(const char *)pixel];
            if (shoucang.src.length != 0) {
                
                shoucang.src = [NSString stringWithUTF8String:(const char *)src];
            }
            
            shoucang.num = num;
            [arr addObject:shoucang];
            
        }
    }else{
        NSLog(@"查询准备失败");
    }
    return arr;

}
//删除航
- (void)deleteInfoWithTitle:(NSString *)title
{
    //1.创建sql语句
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from yuedu where title = '%@'", title];
    //2.执行语句
    int result = sqlite3_exec(dbPoint, deleteSQL.UTF8String, NULL, NULL, nil);
    //3.判断
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
}
//删除表
- (void)dropTable
{
  //1.sql语句
    NSString *dropSQL = @"drop table yuedu";
    //2.执行
    int result = sqlite3_exec(dbPoint, dropSQL.UTF8String, NULL, NULL, nil);
    //3.判断
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}


@end
